package com.hnkywl.sts.dao.cwgl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.dao.cwgl.kmys.CyfyysDao;
import com.hnkywl.sts.dao.cwgl.kmys.CykmdmysDao;
import com.hnkywl.sts.dao.cwgl.kmys.LjkmdmysDao;
import com.hnkywl.sts.dao.cwgl.kmys.NbgskmdmysDao;
import com.hnkywl.sts.dao.organ.GsjgDao;
import com.hnkywl.sts.dao.organ.KhhDao;
import com.hnkywl.sts.entity.cwgl.Khzhsfmx;
import com.hnkywl.sts.entity.cwgl.VoucherItemData;
import com.hnkywl.sts.entity.cwgl.kmys.Cykmdmys;
import com.hnkywl.sts.entity.cwgl.kmys.Ljkmdmys;
import com.hnkywl.sts.entity.cwgl.kmys.Nbgskmdmys;
import com.hnkywl.sts.entity.organ.Gsjg;
import com.hnkywl.sts.entity.organ.Khh;
import com.hnkywl.sts.entity.zjgl.Pj;
import com.hnkywl.sts.entity.zjgl.Pjdr;
import com.hnkywl.sts.service.cwgl.KmdmService;
import com.hnkywl.sts.service.sys.YgService;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.SQLQuery;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-5-14
 * Time: 上午11:26
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class TxcdDao extends SimpleHibernateTemplate<Pjdr, Long> {
    private CykmdmysDao cykmdmysDao;
    private CyfyysDao cyfyysDao;
    private KmdmDao kmdmDao;
    private KmdmService kmdmService;
    private LjkmdmysDao ljkmdmysDao;
    private NbgskmdmysDao nbgskmdmysDao;
    private GsjgDao gsjgDao;
    private KhhDao khhDao;

    /**
     * 查询贴现承兑
     *
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Pjdr> findAllTxcd( Long gsId, String gsMc, Integer txcdZt, Integer voucherNum, String czr, Long djh,Integer pjlx,String rzyh,int pageNo, int pageSize) {
        String hql = null;
        Map<String, Object> param = new HashMap<String, Object>();
        StringBuilder condition = new StringBuilder();
        Page<Pjdr> page = new Page<Pjdr>(pageNo, pageSize, 0);
        //==============条件拼接===============
        if (gsId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" pjdr.nbgs.id = :gsId ");
            param.put("gsId", gsId);
        }
        if (pjlx != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" pjdr.pjlx = :pjlx ");
            param.put("pjlx", pjlx);
        }
        if (djh != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" pjdr.id = :djh ");
            param.put("djh", djh);
        }
        if (gsId == null && gsMc != null && !gsMc.equals("")) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" pjdr.nbgs.jc like '%"+gsMc.trim()+"%' ");
        }
        if ( rzyh != null && !rzyh.equals("")) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" pjdr.rzyh.qc like '%"+rzyh.trim()+"%' ");
        }
        if (voucherNum != null && !voucherNum.equals("")) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" pjdr.voucherNum = :pzh ");
            param.put("pzh", voucherNum);
        }
        if (czr != null && !czr.equals("")) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" pjdr.createUser.xm like '%"+czr.trim()+"%' ");
        }
        if (txcdZt != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            //status=0未导入
            //status=1导入成功
            //status=2导入失败
            //status=3删除成功
            //status=4删除失败
            condition.append(" (:status=0 and insertFlag=1) " +
                    " or (:status=1 and insertFlag=3 and deleteFlag!=2) " +
                    " or (:status=2 and insertFlag=2) " +
                    " or (:status=3 and deleteFlag=3 and insertFlag!=2) " +
                    " or (:status=4 and deleteFlag=2) ");
            param.put("status", txcdZt);
        }


        //==============记录数量===============
        hql = "select count(*) from Pjdr pjdr where pjdr.csh is null    "+ RegionDao.createDataRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        Long c = (Long) createQuery(hql).setProperties(param).uniqueResult();
        page.setTotalCount(c.intValue());
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "   from Pjdr  pjdr where pjdr.csh is null  "+ RegionDao.createDataRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        hql = hql +" order by pjdr.dateCreated desc ";
        List<Pjdr> list = createQuery(hql)
                .setProperties(param)
                .setFirstResult((pageNo - 1) * pageSize)
                .setMaxResults(pageSize).list();
        page.setResult(list);
        return page;
    }


    public List<Khzhsfmx> getKhhzfmx(Long pjId) {
        String hql = " from Khzhsfmx where pjdr.id = ? ";
        return find(hql, pjId);
    }

    public Khh findCpyhByTxcdId(Long txcdId) {
        List<Khzhsfmx> khzhsfmxes = getKhhzfmx(txcdId);
        return khzhsfmxes.get(0).getRzyh();
    }



    /**
     * 通过票据返回凭证部分
     *
     * @param
     * @return
     */
    public List<VoucherItemData> getVoucher(Pjdr pjdr) {
        List<VoucherItemData> voucherItemDatas = new ArrayList<VoucherItemData>();
        //票据贴现的数据
        if (pjdr.getStatus() == 2) {
            List<Khzhsfmx> khzhsfmxes = getKhhzfmx(pjdr.getId());
            Double je2 = 0d;
            for (Khzhsfmx k : khzhsfmxes) {
                if (k.getGllx() == 8) {
                    je2 = k.getJe();
                }
            }
            //支付方式需要带入
            Cykmdmys cykmdmys2 = this.cykmdmysDao.getCommentSubject(pjdr.getNbgs().getId(), "2800");
            String ccname2 = cykmdmys2.getJqcwkmmc();
            String ccode2 = cykmdmys2.getKmdm();
            String ccode1 = khzhsfmxes.get(0).getRzyh().getKmdm() + "";//这里应该是开户行的jiuqicode
            String ccname1 = "银行存款/" + khzhsfmxes.get(0).getRzyh().getQc();
            Double je1 = Double.parseDouble(pjdr.getDqje().toString()) - je2;
            //应付款
            Cykmdmys cykmdmys;
            if (pjdr.getPjsflx() == 1) {
                if (pjdr.getPjlx() == 0) {
                    cykmdmys = this.cykmdmysDao.getCommentSubject(pjdr.getNbgs().getId(), "700");
                } else {
                    cykmdmys = this.cykmdmysDao.getCommentSubject(pjdr.getNbgs().getId(), "701");
                }
            } else {
                if (pjdr.getPjlx() == 0) {
                    cykmdmys = this.cykmdmysDao.getCommentSubject(pjdr.getNbgs().getId(), "600");//银票
                } else {
                    cykmdmys = this.cykmdmysDao.getCommentSubject(pjdr.getNbgs().getId(), "601");// 商票
                }

            }
            String ccode3 = cykmdmys.getKmdm() + "";
            String ccname3 = cykmdmys.getJqcwkmmc();
            List<VoucherItemData> voucherItemDataList = new ArrayList<VoucherItemData>();
            List<Map> items = groupPjByJpdw(pjdr.getId());
            if (je1 != 0) {
                VoucherItemData voucherItemData1 = new VoucherItemData();
                voucherItemData1.setZy("收" + khzhsfmxes.get(0).getRzyh().getQc() + "银行贴现款");
                voucherItemData1.setKmdm(ccode1);
                voucherItemData1.setKmmc(ccname1);
                voucherItemData1.setJf(je1);
                voucherItemDatas.add(voucherItemData1);
            }
            if (je2 != 0) {
                VoucherItemData voucherItemData2 = new VoucherItemData();
                voucherItemData2.setZy("收" + khzhsfmxes.get(0).getRzyh().getQc() + "银行贴现款");
                voucherItemData2.setKmdm(ccode2);
                voucherItemData2.setKmmc(ccname2);
                voucherItemData2.setSupplierCode(khzhsfmxes.get(0).getRzyh().getGysfzhsdm());//贴现利息取银行供应商辅助核算
                voucherItemData2.setSupplierName(khzhsfmxes.get(0).getRzyh().getQc());
                voucherItemData2.setDeptCode(pjdr.getCreateDepartment().getKmdm());
                voucherItemData2.setDeptName(pjdr.getCreateDepartment().getQc());
                voucherItemData2.setJf(je2);

                voucherItemDatas.add(voucherItemData2);
            }
            for(int i = 0;i<items.size();i++){
                VoucherItemData voucherItemData3 = new VoucherItemData();
                voucherItemData3.setZy("收" + khzhsfmxes.get(0).getRzyh().getQc() + "银行贴现款");
                voucherItemData3.setKmdm(ccode3);
                voucherItemData3.setKmmc(ccname3);
                voucherItemData3.setDf(Double.parseDouble(items.get(i).get("dqje").toString()));
                Gsjg jpdw = gsjgDao.get(Long.parseLong(items.get(i).get("jpdw").toString()));
                if(jpdw.className()=="Nbgs"){
                    Nbgskmdmys ljkmdmys = this.nbgskmdmysDao.findKmdmByGsjgId(pjdr.getNbgs().getId(),jpdw.getId(), 0);
                    voucherItemData3.setCustomerCode(ljkmdmys.getKhkmdm());
                }else{
                    Ljkmdmys ljkmdmys = this.ljkmdmysDao.findKmdmByGsjgId(jpdw.getId());
                    voucherItemData3.setCustomerCode(ljkmdmys.getKhfzhsdm());
                }
                voucherItemData3.setCustomerName(jpdw.getQc());
                voucherItemDatas.add(voucherItemData3);
            }
        }

        //票据托收
        if (pjdr.getStatus() == 3) {
            List<Khzhsfmx> khzhsfmxes = getKhhzfmx(pjdr.getId());
            String ccode1 = khzhsfmxes.get(0).getRzyh().getKmdm() + "";//这里应该是开户行的jiuqicode
            String ccname1 = "银行存款/" + khzhsfmxes.get(0).getRzyh().getQc();
            Double je1 = Double.parseDouble(pjdr.getDqje().toString());
            //应付款
            Cykmdmys cykmdmys;
            if (pjdr.getPjsflx() == 1) {
                if (pjdr.getPjlx() == 0) {
                    cykmdmys = this.cykmdmysDao.getCommentSubject(pjdr.getNbgs().getId(), "700");
                } else {
                    cykmdmys = this.cykmdmysDao.getCommentSubject(pjdr.getNbgs().getId(), "701");
                }
            } else {
                if (pjdr.getPjlx() == 0) {
                    cykmdmys = this.cykmdmysDao.getCommentSubject(pjdr.getNbgs().getId(), "600");//银票
                } else {
                    cykmdmys = this.cykmdmysDao.getCommentSubject(pjdr.getNbgs().getId(), "601");// 商票
                }

            }
            String ccode3 = cykmdmys.getKmdm() + "";
            String ccname3 = cykmdmys.getJqcwkmmc();
            List<VoucherItemData> voucherItemDataList = new ArrayList<VoucherItemData>();
            List<Map> items = groupPjByJpdw(pjdr.getId());
            if (je1 != 0) {
                VoucherItemData voucherItemData1 = new VoucherItemData();
                voucherItemData1.setZy("收" + khzhsfmxes.get(0).getRzyh().getQc() + "银行托收款");
                voucherItemData1.setKmdm(ccode1);
                voucherItemData1.setKmmc(ccname1);
                voucherItemData1.setJf(je1);
                voucherItemDatas.add(voucherItemData1);
            }
            for(int i = 0;i<items.size();i++){
                VoucherItemData voucherItemData3 = new VoucherItemData();
                voucherItemData3.setZy("收" + khzhsfmxes.get(0).getRzyh().getQc() + "银行托收款");
                voucherItemData3.setKmdm(ccode3);
                voucherItemData3.setKmmc(ccname3);
                voucherItemData3.setDf(Double.parseDouble(items.get(i).get("dqje").toString()));
                Gsjg tsjpdw =  gsjgDao.get(Long.parseLong(items.get(i).get("jpdw").toString()));
                if(tsjpdw.className()=="Nbgs"){
                    Nbgskmdmys ljkmdmys = this.nbgskmdmysDao.findKmdmByGsjgId(pjdr.getNbgs().getId(),tsjpdw.getId(), 0);
                    voucherItemData3.setCustomerCode(ljkmdmys.getKhkmdm());
                }else{
                    Ljkmdmys ljkmdmys = this.ljkmdmysDao.findKmdmByGsjgId(tsjpdw.getId());
                    voucherItemData3.setCustomerCode(ljkmdmys.getKhfzhsdm());
                }
                voucherItemData3.setCustomerName(tsjpdw.getQc());
                voucherItemDatas.add(voucherItemData3);
            }
        }

        //todo 票据承兑
        if (pjdr.getStatus() == 4) {
            //第三行分录
            Cykmdmys cykmdmys;
            if (pjdr.getPjlx() == 0) {
                cykmdmys = this.cykmdmysDao.getCommentSubject(pjdr.getNbgs().getId(), "700");
            } else {
                cykmdmys = this.cykmdmysDao.getCommentSubject(pjdr.getNbgs().getId(), "701");
            }
            String ccode1 = cykmdmys.getKmdm() + "";
            String ccname1 = cykmdmys.getJqcwkmmc();
            Double je1 = Double.parseDouble(pjdr.getDqje().toString());
            List<Khzhsfmx> khzhsfmxes = getKhhzfmx(pjdr.getId());
            //支付方式需要带入  第二行分录
            Cykmdmys cykmdmys1 = this.cykmdmysDao.getCommentSubject(pjdr.getNbgs().getId(), "2700");
            String ccname2 = cykmdmys1.getJqcwkmmc();
            String ccode2 = cykmdmys1.getKmdm();
            Double je2 = 0d;
            //第一行分录
            String ccode3 = khzhsfmxes.get(0).getRzyh().getKmdm() + "";//这里应该是开户行的jiuqicode
            String ccname3 = "银行存款/" + khzhsfmxes.get(0).getRzyh().getQc();
            Double je3 = Double.parseDouble(pjdr.getDqje().toString()) - je2;
            List<VoucherItemData> voucherItemDataList = new ArrayList<VoucherItemData>();
            List<Map> items = groupPjByFk(pjdr.getId());
            for(int i = 0;i<items.size();i++){
                if(items.get(i).get("dqje") != null){
                VoucherItemData voucherItemData1 = new VoucherItemData();
                voucherItemData1.setZy("还" + khzhsfmxes.get(0).getRzyh().getQc() + "银行银承到期款");
                voucherItemData1.setKmdm(ccode1);
                voucherItemData1.setKmmc(ccname1);
                voucherItemData1.setJf(Double.parseDouble(items.get(i).get("dqje").toString()));
                Gsjg cdgsjg =  gsjgDao.get(Long.parseLong(items.get(i).get("gldw").toString()));
                    voucherItemData1.setSupplierName(khzhsfmxes.get(0).getRzyh().getQc());
                    voucherItemData1.setSupplierCode(khzhsfmxes.get(0).getRzyh().getGysfzhsdm());
                voucherItemDatas.add(voucherItemData1);
                }
            }
            if (je2 != 0d) {
                VoucherItemData voucherItemData2 = new VoucherItemData();
                voucherItemData2.setZy("还" + khzhsfmxes.get(0).getRzyh().getQc() + "银行银承到期款");
                voucherItemData2.setKmdm(ccode2);
                voucherItemData2.setKmmc(ccname2);
                voucherItemData2.setJf(je2);
                voucherItemData2.setSupplierName(khzhsfmxes.get(0).getRzyh().getQc());
                voucherItemData2.setSupplierCode(khzhsfmxes.get(0).getRzyh().getGysfzhsdm());
                voucherItemDatas.add(voucherItemData2);
            }
            if (je3 != 0) {
                VoucherItemData voucherItemData3 = new VoucherItemData();
                voucherItemData3.setZy("还" + khzhsfmxes.get(0).getRzyh().getQc() + "银行银承到期款");
                voucherItemData3.setKmdm(ccode3);
                voucherItemData3.setKmmc(ccname3);
                voucherItemData3.setDf(je3);
                voucherItemDatas.add(voucherItemData3);
            }
        }
        return voucherItemDatas;
    }

    public String getFzhsByCode(Long gsId, String code) {
        return this.kmdmService.findKmdmDetailByGs(gsId, code).getFzhslx();
    }



    public Integer praseLongToIntegr(Long l) {
        return Integer.parseInt(l.toString());
    }

    public List getVoucherMaster(Integer id) {
        String sql = "select dbo.gf_cw_sys_csign(dbo.gf_sys_cwcompid_by_compid(c.company_id)," +
                "dbo.gf_cw_sys_isignseq(dbo.gf_sys_cwcompid_by_compid(c.company_id),b.jiuqitype))," +
                "c.company_name,c.company_code,c.company_id from t_cw_bank b,t_jiuqi_company c,t_cw_pj_bill p " +
                "where p.bankid=b.bankid and dbo.gf_sys_cwcompid_by_compid(p.compid)=c.company_id and p.billid=?";
        Object[] result = (Object[]) createSQLQuery(sql, id).uniqueResult();
        if (result != null)
            return Arrays.asList(result);
        return null;
    }

    /**
     * 通过交票单位分组票据
     * @return
     */
    public List<Map> groupPjByJpdw(Long pjdrId){
        String sql = " select jpdw_id as \"jpdw\" , sum(dqje)  as \"dqje\"  " +
                "  from t_biz_pj  " +
                "    where pjdr_id = ?   " +
                "    group by jpdw_id ";
        return  (List<Map>)getSession().createSQLQuery(sql).setLong(0,pjdrId).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
    }

    /**
     * 通过付款单位分组票据
     * @return
     */
    public List<Map> groupPjByFk(Long pjdrId){
        String sql = " select sum(pj.dqje) as \"dqje\" ,8860 as \"gldw\"  from t_biz_pj pj where oldid is not null " +
                " and pj.pjdr_id = ?  " +
                " union " +
                " select sum(pj.dqje) as \"dqje\" ,fk.gldw_id as \"gldw\" from t_biz_pj pj  " +
                " left join t_biz_sfk fk on pj.sfk_id = fk.id " +
                " where pj.oldid is null  and pj.pjsflx = 1 and pj.deleted = 0  " +
                " and pj.pjdr_id = ?  " +
                "  group by fk.gldw_id  ";
        return  (List<Map>)getSession().createSQLQuery(sql).setLong(0,pjdrId).setLong(1,pjdrId).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
    }

    //撤销凭证
    public String revokeNote(Pj pj, Long userId) throws SQLException {
        return "";
    }

    public String checkYs(String name) {
        return name + "映射没有配置!";
    }


    @Autowired
    public void setCykmdmysDao(CykmdmysDao cykmdmysDao) {
        this.cykmdmysDao = cykmdmysDao;
    }

    @Autowired
    public void setCyfyysDao(CyfyysDao cyfyysDao) {
        this.cyfyysDao = cyfyysDao;
    }

    @Autowired
    public void setKmdmDao(KmdmDao kmdmDao) {
        this.kmdmDao = kmdmDao;
    }

    @Autowired
    public void setKmdmService(KmdmService kmdmService) {
        this.kmdmService = kmdmService;
    }

    @Autowired
    public void setLjkmdmysDao(LjkmdmysDao ljkmdmysDao) {
        this.ljkmdmysDao = ljkmdmysDao;
    }
    @Autowired
    public void setNbgskmdmysDao(NbgskmdmysDao nbgskmdmysDao) {
        this.nbgskmdmysDao = nbgskmdmysDao;
    }
    @Autowired
    public void setGsjgDao(GsjgDao gsjgDao) {
        this.gsjgDao = gsjgDao;
    }
    @Autowired
    public void setKhhDao(KhhDao khhDao) {
        this.khhDao = khhDao;
    }
}
